package logobj.dao;

import logobj.entity.analysis.XnqcJbxxVo;
import logobj.entity.bak.Dbdw;
import logobj.entity.bak.Shzip;
import logobj.utils.MD5Utils;
import logobj.utils.OutLog;
import logobj.utils.TimeUtils;
import logobj.utils.db.DBCon;
import logobj.config.ConfProperties;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class LogDao {


    //清除表数据
    public void clearTable(){
        String[] tablenameArr = ConfProperties.trunca_tetable_name.split(",");
        String delsql="";
        String subsql = "";
        if("gp".equals(ConfProperties.dbtype)){
            subsql = "  AND sjrq <= to_char(now()+interval '-1 day','yyyy-MM-dd')";
        }else if("m5".equals(ConfProperties.dbtype) || "m8".equals(ConfProperties.dbtype)){
            subsql = "  AND sjrq <= DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d') ";
        }
        PreparedStatement pstmt = null;
        try {
          //  DBCon.getConnection().setAutoCommit(false);
            int delrow = 0;
            for(int i=0; i< tablenameArr.length ; i++ ){
                delsql =  "DELETE FROM "+tablenameArr[i]+" WHERE 1 = 1  " + subsql;
                pstmt = DBCon.getConnection().prepareStatement(delsql);
                delrow = pstmt.executeUpdate();
                OutLog.printf("执行删除数据SQL:" + delsql+" :已删除："+delrow +"条");
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    //插入数据
    public void insertXnqcData(ArrayList<XnqcJbxxVo> xnqcJbxxVoArrayList){
        //获取数据库连接
        Connection con = DBCon.getConnection();
        PreparedStatement pre = null;
        String insertsql="INSERT INTO  t_xnqc_jbxx(machine, src_ip, basic_tag, assets_name, severity, lat, lng, gjsj, sjrq) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            pre = con.prepareStatement(insertsql);
            XnqcJbxxVo xnqcJbxxVo =null;
            for(int i = 0; i<xnqcJbxxVoArrayList.size();i++){
                xnqcJbxxVo = xnqcJbxxVoArrayList.get(i);
                pre.setString(1, xnqcJbxxVo.getMachine());
                pre.setString(2, xnqcJbxxVo.getSrc_ip() );
                pre.setString(3, xnqcJbxxVo.getBasic_tag() );
                pre.setString(4, xnqcJbxxVo.getAssets_name() );
                pre.setString(5,xnqcJbxxVo.getSeverity() );
                pre.setString(6,xnqcJbxxVo.getLat() );
                pre.setString(7,xnqcJbxxVo.getLng() );
                pre.setTimestamp(8, xnqcJbxxVo.getGjsj() );
                pre.setString(9, xnqcJbxxVo.getRkrq() );
                pre.addBatch();
            }
            pre.executeBatch();
            pre.clearBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                pre.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }




    //插入数据
    public  int insertData(Map<String , Object> jsonmap ,int updaterow){
        //获取数据库连接
        Connection con = DBCon.getConnection();
        PreparedStatement pre = null;
        List<Dbdw> dbdwArrayList = (List<Dbdw>)jsonmap.get("dbdwArrayList");
        List<Shzip> shzipArrayList = (List<Shzip>)jsonmap.get("shzipArrayList");

        String insert_sql_dbdw =  "INSERT INTO \"public\".\"t_xnqc_dbdw\"(\"status\"" +
                ", \"intel_uuid\", \"ioc_type\", \"intel_type\", \"severity\", \"confidence\"," +
                " \"family\", \"source\", \"main_tag\", \"is_apt\", \"source_type\", \"basic_tag\"" +
                ", \"check_type\", \"sjrq\", \"machine\", \"assets_id\", \"assets_status\", \"assets_name\"" +
                ", \"assets_ip\", \"assets_section\", \"assets_location\", \"assets_level\", \"assets_ext\", \"assets_mac\") " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        String insert_sql_shzip =   "INSERT INTO \"public\".\"t_xnqc_shzip\"(\"status\", \"intel_uuid\"," +
                " \"ioc_type\", \"intel_type\", \"severity\", \"confidence\", \"family\", \"source\", " +
                "\"main_tag\", \"is_apt\", \"source_type\", \"basic_tag\", \"check_type\", \"sjrq\", \"machine\", \"mc_geo_latitude\", \"mc_geo_longitude\") " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)";

        String insert_ssll_sql="INSERT INTO \"public\".\"t_xnqc_ssll\"( \"ip\", \"domain\", \"sjrq\") VALUES (?, ?, ?)";


        int rownum = dbdwArrayList.size();
        Dbdw dbdw = null ;
        Shzip shzip = null;
        //关闭自动提交事务
        try {
          //  con.setAutoCommit(false);
            if(0 != rownum){
                pre = con.prepareStatement(insert_sql_dbdw);
                for(int i = 0 ; i< rownum ; i++ ){
                    dbdw = dbdwArrayList.get(i);
                    pre.setString(1, dbdw.getStatus());
                    pre.setString(2, dbdw.getIntel_uuid() );
                    pre.setString(3, dbdw.getIoc_type() );
                    pre.setString(4, dbdw.getIntel_type() );
                    pre.setString(5, dbdw.getSeverity() );
                    pre.setString(6, dbdw.getConfidence() );
                    pre.setString(7, dbdw.getFamily() );
                    pre.setString(8, dbdw.getSource() );
                    pre.setString(9, dbdw.getMain_tag() );
                    pre.setString(10, dbdw.getIs_apt() );
                    pre.setString(11, dbdw.getSource_type() );
                    pre.setString(12, dbdw.getBasic_tag() );
                    pre.setString(13, dbdw.getCheck_type() );
                    pre.setString(14, TimeUtils.getStringNowDate("yyyy-MM-dd") );
                    pre.setString(15, dbdw.getMachine() );
                    pre.setString(16, dbdw.getAssets_id() );
                    pre.setString(17, dbdw.getAssets_status() );
                    pre.setString(18, dbdw.getAssets_name() );
                    pre.setString(19, dbdw.getAssets_ip() );
                    pre.setString(20, dbdw.getAssets_section() );
                    pre.setString(21, dbdw.getAssets_location() );
                    pre.setString(22, dbdw.getAssets_level() );
                    pre.setString(23, dbdw.getAssets_ext() );
                    pre.setString(24, dbdw.getAssets_mac() );
                    //把数据放入缓存区
                    pre.addBatch();
                    if(i%ConfProperties.update_rownum  == 0 && i != 0){
                        //执行提交
                        pre.executeBatch();
                        //清除数据缓存
                        pre.clearBatch();
                    }
                }
                //执行提交
                pre.executeBatch();
                //清除数据缓存
                pre.clearBatch();

                pre = con.prepareStatement(insert_ssll_sql);
                for(int i = 0 ; i< rownum ; i++ ){
                    dbdw = dbdwArrayList.get(i);
                    pre.setString(1, dbdw.getMachine() );
                    pre.setString(2, dbdw.getAssets_name());
                    pre.setString(3, TimeUtils.getStringNowDate("yyyy-MM-dd") );
                    //把数据放入缓存区
                    pre.addBatch();
                    //如果小于批量提交数并且已经加载完，则进行提交
                    if(i%ConfProperties.update_rownum  == 0 && i != 0){
                        //执行提交
                        pre.executeBatch();
                        //清除数据缓存
                        pre.clearBatch();
                    }
                }
                //执行提交
                pre.executeBatch();
                //清除数据缓存
                pre.clearBatch();
            }

            rownum = shzipArrayList.size();
            if( 0 != rownum){
                pre = con.prepareStatement(insert_sql_shzip);
                for(int i = 0 ;i < rownum ; i++){
                    shzip = shzipArrayList.get(i);
                    pre.setString(1, shzip.getStatus() );
                    pre.setString(2, shzip.getIntel_uuid() );
                    pre.setString(3, shzip.getIoc_type() );
                    pre.setString(4, shzip.getIntel_type() );
                    pre.setString(5, shzip.getSeverity() );
                    pre.setString(6, shzip.getConfidence() );
                    pre.setString(7, shzip.getFamily() );
                    pre.setString(8, shzip.getSource() );
                    pre.setString(9, shzip.getMain_tag() );
                    pre.setString(10, shzip.getIs_apt() );
                    pre.setString(11, shzip.getSource_type() );
                    pre.setString(12, shzip.getBasic_tag() );
                    pre.setString(13, shzip.getCheck_type() );
                    pre.setString(14, TimeUtils.getStringNowDate("yyyy-MM-dd") );
                    pre.setString(15, shzip.getMachine() );
                    pre.setString(16, shzip.getMc_geo_latitude());
                    pre.setString(17, shzip.getMc_geo_longitude());
                    //把数据放入缓存区
                    pre.addBatch();
                    //如果小于批量提交数并且已经加载完，则进行提交
                    if(i%ConfProperties.update_rownum  == 0 && i != 0){
                        //执行提交
                        pre.executeBatch();
                        //清除数据缓存
                        pre.clearBatch();
                    }
                    updaterow += 1;
                    // int allrow = logDao.selectTxnqcshzipCount();
                    if(updaterow >= ConfProperties.data_warehousing_shz_maxrow ){
                        OutLog.printf("本次完成入库数据条数："+ updaterow);
                        updaterow = 0;
                        break;
                    }
                }
                //执行提交
                pre.executeBatch();
                //清除数据缓存
                pre.clearBatch();

            }
            pre.close();
           // con.commit();//手动提交事务
          //  con.setAutoCommit(true);//在把自动提交打开
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            //DBCon.CloseCon();
        }
        return updaterow;
    }


    //插入今天的等保单位统计数据
    public void insertDbdwTj(Map<String,Long> tjmap){
        //获取数据库连接
        Connection con = DBCon.getConnection();
        PreparedStatement pre = null;
        String insert_sql_dbdwtj="INSERT INTO \"public\".\"t_xnqc_dbdw_tj\"(\"pk_id\",\"sjrq\", \"cs\") VALUES (?,?,?)";
        //写入统计数
        try {
            pre = con.prepareStatement(insert_sql_dbdwtj);
            Iterator<Map.Entry<String,Long>> itor = tjmap.entrySet().iterator();
            Map.Entry<String,Long> map = null;
            while (itor.hasNext()){
                map = itor.next();
                pre.setString(1, MD5Utils.MD5( TimeUtils.getTimestamp() +""+map.getValue() ) );
                pre.setString(2,  map.getKey());
                pre.setInt(3,  map.getValue().intValue());
                pre.addBatch();
            }
            pre.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(pre !=null){
                    pre.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
           // DBCon.CloseCon();
        }

    }


    //查询单值
    public Object selectOneValue(String sql){
        Connection conn = DBCon.getConnection();
        PreparedStatement pre = null;
        ResultSet rs = null;
        Object value = null;
        try {
            pre = conn.prepareStatement(sql);
            rs = pre.executeQuery();
            while (rs.next()){
                value = rs.getObject(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return value;
    }
}
